import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler, MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, \
    ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
from Predict_6628_2Job import Predict_6628_2Job
from Predict_6629_2Job import Predict_6629_2Job

#必传,KR后目标硫
pret_s_aim = 14
# 改变前的铁水S与改变后的铁水S，铁水包受铁净铁水量。必传
avg_s_value_before = 30
avg_s_value_after = 50
iron_wt_net = 2700
# 改变前的温降系数、脱硫剂重量、脱硫时间，非必传，温降系数为空字符串则使用6628_2直接计算温度，改变前的重量和时间为空字符串则会根据规则计算
# wenjiang_before = 34
wenjiang_coef = ''
cao_wt_before = ''
whisk_time_before = ''
#其他参数,必传,用于计算成本
coef1 = 5
coef2 = 1
coef3 = 0.005
coef4 = 2.3
coef5 = 0.4
# 带铁成本
iron_price = 3000
# 温降成本
wenjiang_price = 1
# 脱硫剂成本
cao_price = 0.91

pret_s_aim = int(pret_s_aim)

df2, df3 = Predict_6629_2Job(p_mode=1).execute()

df2.columns = df2.columns.str.upper()
df3.columns = df3.columns.str.upper()

df2['AVG'] = 0.33 * df2['IW'] + 0.17 * (df2['AIM_14'] + df2['AIM_24']) + 0.17 * (df2['AIM_34'] + df2['AIM_54'])
row1 = df2[df2['RECV_S_MAX'] == 300]
value1 = row1['AVG'].values[0] if not row1.empty else 0
row2 = df2[df2['RECV_S_MAX'] == 500]
value2 = row2['AVG'].values[0] if not row2.empty else 0
dh_unit_1 = (value2 - value1) / 20
# 受铁后S
# recv_s = df1['RECV_S'].values[0]
column_name_list = df2.columns.tolist()
print(column_name_list)
column_name_tmp = 'AIM_' + str(int(pret_s_aim))
df2_tmp = df2[['RECV_S_MAX', column_name_tmp]]
# if st_no[:2] == 'IW':
if int(pret_s_aim)!=14 and int(pret_s_aim)!=24 and int(pret_s_aim)!=34 and int(pret_s_aim)!=54:
    column_name_tmp = 'IW'
df2_tmp = df2[['RECV_S_MAX', column_name_tmp]]
# 单耗,CAO复合单耗
df2_tmp.rename(columns={column_name_tmp: 'DH'}, inplace=True)
df2_tmp['RECV_S_MAX_NEXT'] = df2_tmp['RECV_S_MAX'].shift(-1)
df2_tmp['DH_NEXT'] = df2_tmp['DH'].shift(-1)
df2_tmp.RECV_S_MAX_NEXT.fillna(99999, inplace=True)
df2_tmp.DH_NEXT.fillna(99999, inplace=True)
# xlsx_name = 'D:/repos/sicost/二炼钢脱硫模型时间.xlsx'
# df3 = pd.read_excel(xlsx_name)
# df3.columns = df3.columns.str.upper()
row3 = df3[df3['RECV_S_MAX'] == 300]
value3 = row3['WHISK_TIME'].values[0] if not row3.empty else 0
row4 = df3[df3['RECV_S_MAX'] == 500]
value4 = row4['WHISK_TIME'].values[0] if not row4.empty else 0
whisk_time_unit_1 = (value4 - value3) / 20
df3['RECV_S_MAX_NEXT'] = df3['RECV_S_MAX'].shift(-1)
df3['WHISK_TIME_NEXT'] = df3['WHISK_TIME'].shift(-1)
df3.RECV_S_MAX_NEXT.fillna(99999, inplace=True)
df3.WHISK_TIME_NEXT.fillna(99999, inplace=True)

def cal_dh_whisk_time(p_recv_s):
    recv_s = p_recv_s
    for index, row in df2_tmp.iterrows():
        recv_s_max_tmp = row['RECV_S_MAX']
        dh_max_tmp = row['DH']
        recv_s_max_next_tmp = row['RECV_S_MAX_NEXT']
        dh_max_next_tmp = row['DH_NEXT']
        print(index)
        if recv_s <= 500:
            dh_unit = (dh_max_next_tmp - dh_max_tmp) / (recv_s_max_next_tmp - recv_s_max_tmp) * 10
        else:
            dh_unit = dh_unit_1
        if index == 0:
            if recv_s <= recv_s_max_tmp:
                dh_tmp = dh_max_tmp - (recv_s_max_tmp - recv_s) * dh_unit / 10
                break
            elif recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                dh_tmp = dh_max_tmp + (recv_s - recv_s_max_tmp) * dh_unit
                break
            else:
                continue
        else:
            if recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                dh_tmp = dh_max_tmp + (recv_s - recv_s_max_tmp) * dh_unit / 10
                break
            else:
                continue
    for index, row in df3.iterrows():
        recv_s_max_tmp = row['RECV_S_MAX']
        whisk_time_max_tmp = row['WHISK_TIME']
        recv_s_max_next_tmp = row['RECV_S_MAX_NEXT']
        whisk_time_max_next_tmp = row['WHISK_TIME_NEXT']
        print(index)
        if recv_s <= 500:
            whisk_time_unit = (whisk_time_max_next_tmp - whisk_time_max_tmp) / (
                    recv_s_max_next_tmp - recv_s_max_tmp) * 10
        else:
            whisk_time_unit = whisk_time_unit_1
        if index == 0:
            if recv_s <= recv_s_max_tmp:
                whisk_time_tmp = whisk_time_max_tmp - (recv_s_max_tmp - recv_s) * whisk_time_unit / 10
                break
            elif recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                whisk_time_tmp = whisk_time_max_tmp + (recv_s - recv_s_max_tmp) * whisk_time_unit
                break
            else:
                continue
        else:
            if recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                whisk_time_tmp = whisk_time_max_tmp + (recv_s - recv_s_max_tmp) * whisk_time_unit / 10
                break
            else:
                continue
    return dh_tmp, whisk_time_tmp

# recv_s_before = 30
# recv_s_after = 50
recv_s_before = avg_s_value_before
recv_s_after = avg_s_value_after
recv_s_delta = recv_s_after - recv_s_before
wt_tmp, whisk_time_tmp = cal_dh_whisk_time(recv_s_before * 10)
if cao_wt_before != '':
    cao_wt_before = float(cao_wt_before)
else:
    cao_wt_before = wt_tmp
if whisk_time_before != '':
    whisk_time_before = float(whisk_time_before)
else:
    whisk_time_before = whisk_time_tmp
cao_wt_after, whisk_time_after = cal_dh_whisk_time(recv_s_after * 10)
# iron_wt = df1['IRON_WT_NET'].values[0] / 10
wt_tmp = cao_wt_before
wt_tmp_ad = cao_wt_after
iron_wt = iron_wt_net/10
dh_tmp = wt_tmp / iron_wt
dh_tmp_ad = wt_tmp_ad / iron_wt


whisk_time_tmp = whisk_time_before
whisk_time_tmp_ad = whisk_time_after
whisk_time_tmp_delta = whisk_time_tmp_ad - whisk_time_tmp
cao_wt = wt_tmp/1000
cao_wt_ad = wt_tmp_ad/1000
cao_wt_delta = cao_wt_ad - cao_wt
# xlsx_name = 'D:/repos/sicost/二炼钢成本参数.xlsx'
# df3 = pd.read_excel(xlsx_name)
# coef1 = df3['炉均扒渣量'].values[0]
# coef2 = df3['高炉渣'].values[0]
# coef3 = df3['扒渣量增加'].values[0]
# coef4 = df3['铁水包成本'].values[0]
# coef5 = df3['搅拌桨成本'].values[0]
# coef6 = df3['热轧品种的边际贡献'].values[0]
# 扒渣带铁量(t/包)
coef7 = coef1 - coef2 - cao_wt
# 30S扒渣带铁比例(%)
coef8 = coef7 / coef1
# 50S扒渣带铁比例(%)
coef9 = coef8 + 0.1
# 耐材增加/S
coef10 = whisk_time_tmp_delta / whisk_time_tmp / recv_s_delta
# 扒渣带铁
daitie = coef1 * coef8 / iron_wt
daitie_ad = (coef1 + cao_wt_delta) * (1 + coef3 * recv_s_delta) * coef9 / iron_wt

if wenjiang_coef != '':
    wenjiang_coef = float(wenjiang_coef)
    wenjiang = wenjiang_coef * whisk_time_tmp
    wenjiang_ad = wenjiang_coef * whisk_time_tmp_ad
else:
    # 温降与脱硫时间的转换系数，如果没有传入则使用历史数据拟合
    # msg, wenjiang_coef_tmp = Predict_6626Job(p_mode=1, p_st_no=st_no, p_aim_st_s=aim_st_s).execute()
    # # wenjiang_coef_tmp = 1
    # wenjiang_coef = wenjiang_coef_tmp
    # wenjiang = wenjiang_coef * whisk_time_tmp
    # wenjiang_ad = wenjiang_coef * whisk_time_tmp_ad
    wenjiang = Predict_6628_2Job(p_pret_s_aim=pret_s_aim,p_recv_s=recv_s_before).execute()
    wenjiang_ad = Predict_6628_2Job(p_pret_s_aim=pret_s_aim,p_recv_s=recv_s_after).execute()


# temp_down = 35
# temp_down_ad = whisk_time_tmp_ad / whisk_time_tmp * temp_down
# 耐材成本
naicai_cost = coef4 + coef5
naicai_cost_ad = naicai_cost * recv_s_delta * coef10
# 带铁成本
# iron_price = 3000
daitie_cost = daitie * iron_price
daitie_cost_ad = daitie_ad * iron_price
# 温降成本
# temp_down_price = 1
wenjiang_cost = wenjiang * wenjiang_price
wenjiang_cost_ad = wenjiang_ad * wenjiang_price
# 脱硫剂成本
# cao_price = 0.91
tuoliuji_cost = dh_tmp * cao_price
tuoliuji_cost_ad = dh_tmp_ad * cao_price

total_cost = naicai_cost + daitie_cost + wenjiang_cost + tuoliuji_cost
total_cost_ad = naicai_cost_ad + daitie_cost_ad + wenjiang_cost_ad + tuoliuji_cost_ad
df_out = pd.DataFrame(columns=['PLAN_NAME', 'CAO_DH', 'WHISK_TIME', 'DAITIE', 'WENJIANG',
                               'NAICAI_COST', 'DAITIE_COST', 'WENJIANG_COST', 'TUOLIUJI_COST', 'TOTAL_COST'])
dict = {}

dict['PLAN_NAME'] = '调整铁水硫前'
dict['CAO_DH'] = dh_tmp
dict['WHISK_TIME'] = whisk_time_tmp
dict['DAITIE'] = daitie
dict['WENJIANG'] = wenjiang
dict['NAICAI_COST'] = naicai_cost
dict['DAITIE_COST'] = daitie_cost
dict['WENJIANG_COST'] = wenjiang_cost
dict['TUOLIUJI_COST'] = tuoliuji_cost
dict['TOTAL_COST'] = total_cost
new_row = pd.Series(dict)
df_out = df_out.append(new_row, ignore_index=True)
dict = {}

dict['PLAN_NAME'] = '调整铁水硫后'
dict['CAO_DH'] = dh_tmp_ad
dict['WHISK_TIME'] = whisk_time_tmp_ad
dict['DAITIE'] = daitie_ad
dict['WENJIANG'] = wenjiang_ad
dict['NAICAI_COST'] = naicai_cost_ad
dict['DAITIE_COST'] = daitie_cost_ad
dict['WENJIANG_COST'] = wenjiang_cost_ad
dict['TUOLIUJI_COST'] = tuoliuji_cost_ad
dict['TOTAL_COST'] = total_cost_ad
new_row = pd.Series(dict)
df_out = df_out.append(new_row, ignore_index=True)
msg = '运行成功'
result_list = df_out.to_dict(orient='records')
